#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import pymysql
import datetime, time
import random
import requests
import json
from settings import my_settings

settings = my_settings.mysqlSettings(hostname='jyb_mysql')
settings = settings.mysql_conf
MYSQL_HOST = settings['MYSQL_HOST']
MYSQL_PORT = settings['MYSQL_PORT']
MYSQL_DB = settings['MYSQL_DB']
MYSQL_USER = settings['MYSQL_USER']
MYSQL_PASSWD = settings['MYSQL_PASSWD']
MYSQL_CHARTSET = settings['MYSQL_CHARTSET']

from tqdm import tqdm

import os
from urllib import request
from urllib.request import urlretrieve

import xlrd
def read_excel_data(filepath):
    workbook = xlrd.open_workbook(filepath)

    sheet = workbook.sheet_by_index(0)
    is_right_data=False
    result_datas=[]
    for index in range(1, sheet.nrows):
        row_value = sheet.row_values(index)
        row_value=list(filter(None,row_value))
        if len(row_value)!=8:
            continue
        elif len(row_value)==8 and row_value==['FUND', 'Date', 'Direction', 'Ticker', 'CUSIP', 'Name', 'Shares', '% of ETF']:
            is_right_data=True
        elif len(row_value)==8 and is_right_data:
            result_datas.append(tuple(row_value))
    return result_datas

if __name__ == '__main__':
    # db = pymysql.connect(
    #     host=MYSQL_HOST,
    #     port=MYSQL_PORT,
    #     database=MYSQL_DB,
    #     user=MYSQL_USER,
    #     password=MYSQL_PASSWD,
    #     charset=MYSQL_CHARTSET)
    # cursor = db.cursor()
    #
    # cursor.close()
    # db.close()

    # #有效下载excel文件代码
    # dir = os.path.abspath('.')
    # work_path = os.path.join(dir, 'baidu.xls')
    # url = 'https://u4959697.ct.sendgrid.net/ls/click?upn=zcd6lV4HLKVOGeJ9ek2kSRXRFFQn1rBhvTyMa9aIC2TSfz2mEl5lAXMHUHfvzFMG9EBHI0L9cI-2FhIAdlGLHKOAbmkWWcZh6bmWmTWy343HcPDQALvVV4bjOeIWm1JXQYuNBg_oN-2BONGyN1BoCeXmvWpYlkxTAeDPnqe6UdQS7FFVVG9ex-2BdwsrQpud8I0NL-2BSPgHWQxTxT08968ATXgi5xxO3DBTAy6WTb-2FQ0ha5GPpcpbW-2FEVtI3CeJM50AWlebfXO8vH2d52iBye-2Fmc6yIzhGDGYkx14GY7zWlgpvVMXr7bzhj5ldk3S8Ucj7-2BQrfmH-2BtEptSosNVs4PacYfHeFtuasW5d4EQRWmi5PFKZJ-2FqOf2TowOtHY-2BhSsYyejH5yWg3zfZoOZr1-2B6wxDGodflR40dDzR7vHe3EPph8PMiEV8bS4U-3D'
    # opener=request.build_opener()
    # opener.addheaders=[('User-Agent','Mozilla/5.0 (Linux; U; Android 11; zh-cn; PDEM30 Build/RKQ1.200710.002) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/70.0.3538.80 Mobile Safari/537.36 HeyTapBrowser/40.7.11.3'),
    #                    ('Referer','https://www.baidu.com/')]
    # request.install_opener(opener)
    # request.urlretrieve(url,work_path)

    # # xlrd读取excel代码
    filepath='baidu.xls'
    # result_datas=read_excel_data(filepath)
    # for row in result_datas:
    #     print(row)

    # import re
    # url = 'https://u4959697.ct.sendgrid.net/ls/click?upn=zcd6lV4HLKVOGeJ9ek2kSRXRFFQn1rBhvTyMa9aIC2TSfz2mEl5lAXMHUHfvzFMG9EBHI0L9cI-2FhIAdlGLHKOAbmkWWcZh6bmWmTWy343HcPDQALvVV4bjOeIWm1JXQYuNBg_oN-2BONGyN1BoCeXmvWpYlkxTAeDPnqe6UdQS7FFVVG9ex-2BdwsrQpud8I0NL-2BSPgHWQxTxT08968ATXgi5xxO3DBTAy6WTb-2FQ0ha5GPpcpbW-2FEVtI3CeJM50AWlebfXO8vH2d52iBye-2Fmc6yIzhGDGYkx14GY7zWlgpvVMXr7bzhj5ldk3S8Ucj7-2BQrfmH-2BtEptSosNVs4PacYfHeFtuasW5d4EQRWmi5PFKZJ-2FqOf2TowOtHY-2BhSsYyejH5yWg3zfZoOZr1-2B6wxDGodflR40dDzR7vHe3EPph8PMiEV8bS4U-3D'
    # r=re.findall('',url)